Mega Code Archive
Lookup Reference Functions
1) =ADDRESS(2,3) returns Absolute reference
2) =ADDRESS(2,3,1,FALSE,[Book1]Sheet1) return absolute reference to another workbook and worksheet
3) =ADDRESS(2,3,1,FALSE,EXCEL SHEET) return absolute reference to another worksheet
4) =ADDRESS(2,3,2) return absolute row; relative column
5) =ADDRESS(2,3,2,FALSE) return absolute row; relative column in R1C1 reference style
6) =AREAS(B2
7) =CHOOSE(2, One, Two, Three)
8) =CHOOSE(4,B2,B3,B4,B5)
9) =COLUMN() returns column in which the formula appears
10) =COLUMN(D3
11) =COLUMN(D3)
12) =COLUMN(Scores) return the first column of the Scores range
13) =COLUMNS({1,2,3;4,5,6}) returns the number of columns in the array constant
14) =FIND(birthday,A1)
15) =FIND(y,A1,10)
16) =HLOOKUP(Name,A1
17) =HLOOKUP(Salary,A1
18) =INDEX((A1
19) =INDEX({1,2;3,4},0,2) returns the value in the first row, second column in the array constant
20) =INDEX(A2
21) =INDEX(B1
22) =INDIRECT($A$3) get the value of the reference in cell A3
23) =INDIRECT(B&$A$5) returns the value of a reference in cell A5
24) =LOOKUP(0,A2
25) =LOOKUP(5,A2
26) =LOOKUP(6 66,A2
27) =LOOKUP(A3,{3,6,7,8,9},{F,D,C,B,A})
28) =LOOKUP(A4,{0,70,73,77,80,83,87,90,93,97},{F,D+,C-,C,C+,B-,B,B+,A-,A,A+})
29) =LOOKUP(abcd,{a,1;b,2;c,3})
30) =MATCH(0,A2
31) =MATCH(3,A2
32) =MATCH(8,A2
33) =OFFSET(A3,0,-2)
34) =OFFSET(A3,-2,0)
35) =OFFSET(C3
36) =OFFSET(C3,2,3,1,1) displays the value in cell F5
37) =ROW() returns the row number in which the formula appears
38) =ROW(C4
39) =ROW(Scores) returns the first row of the Scores range
40) =SUM(B2
41) =SUM(CHOOSE(2,B1
42) =SUM(INDEX(A1
43) =SUM(OFFSET (A3,1,2,50,1))
44) =SUM(OFFSET(C3
45) =VLOOKUP( 7,A2
46) =VLOOKUP(0 1,A2
47) =VLOOKUP(1,A1
48) Always refer to cell A10
49) AREAS returns the number of areas in a reference
50) CHOOSE(index_num,value1,value2, ) chooses a value from a list of values
51) COLUMN(reference) returns the column number of a reference
52) COLUMNS returns the number of columns in an array or reference
53) INDEX Uses an index to choose a value from a reference or array
54) INDIRECT(ref_text,a1) returns a reference indicated by a text value
55) LOOKUP looks up values from a one-row or one-column range or an array
56) LOOKUP(lookup_value,array)
57) MATCH(lookup_value,lookup_array,match_type) returns the relative position of an item in an array
58) OFFSET(reference,rows,cols,height,width) returns a reference offset from a given reference
59) Return a worksheet column letter (ranging from A to XFD) for the value contained in cell A1
60) ROW(reference) returns the row number of a reference
61) ROWS(array) returns the number of rows in a reference
62) To specify several references as a single argument, include extra sets of parentheses
63) TRANSPOSE(array) returns the transpose of an array